package net.wantong.tech.xa.dao;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

/**
 * 删除历史数据DAO
 *
 * @author MaShibo
 * 日期 :  2019/9/4
 */
@Repository
public class DeleteHisDataDao {


    private final JdbcTemplate jdbcTemplate;

    public DeleteHisDataDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }


    /**
     * 清空临时表数据
     */
    public void truncateTempTable() {
        final String sql = "truncate table dl_temp_data";
        jdbcTemplate.execute(sql);
    }


    /**
     * 查询临时表数据量
     *
     * @return 数量
     */
    public int queryTempDataCnt() {
        final String sql = "select count(1) from dl_temp_data";
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }


    /**
     * 插入临时表数据
     *
     * @param startOn 起始交易日期
     * @param endOn   结束交易日期
     * @return 插入数量
     */
    public int insertTempData(Date startOn, Date endOn) {
        final String sql = "INSERT INTO dl_temp_data\n" +
                "  (row_id)\n" +
                "  SELECT ROWID\n" +
                "    FROM tr_transout tr\n" +
                "   WHERE tr.opton >= ?\n" +
                "     AND tr.opton < ? + 1\n" +
                "     AND tr.clearflag = 1\n" +
                "     AND tr.clearsucessdt IS NOT NULL";
        return jdbcTemplate.update(sql, startOn, endOn);
    }


    /**
     * 查询待删除的rowId
     *
     * @param date       日期
     * @param processNum 选择数量
     * @return rowId集合
     */
    public List<String> querySelectedRowIds(Date date, int processNum) {
        final String sql =
                "SELECT ROWID\n" +
                        "  FROM tr_transout tr\n" +
                        " WHERE tr.opton >= ?\n" +
                        "   AND tr.opton < ? + 1\n" +
                        "   AND tr.clearflag = 1\n" +
                        "   AND tr.clearsucessdt IS NOT NULL\n" +
                        "   AND rownum <= ?\n";
        return jdbcTemplate.queryForList(sql, String.class, date, date, processNum);
    }


    /**
     * 根据行号查询transout的rowId
     *
     * @param startRow 起始行号
     * @param endRow   结束行号
     * @return rowId集合
     */
    public List<String> queryTransOutRowIdsByRowNum(int startRow, int endRow) {
        final String sql =
                "SELECT row_id\n" +
                        "  FROM (SELECT t.row_id, rownum AS rm FROM dl_temp_data t WHERE rownum <= ?)\n" +
                        " WHERE rm >= ?";
        return jdbcTemplate.queryForList(sql, String.class, endRow, startRow);
    }


    /**
     * 根据rowId批量删除交易流水
     *
     * @param rowIds rowId集合
     * @return 删除数量
     */
    public int[] batchDeleteTransOut(final List<String> rowIds) {
        final String sql = "DELETE FROM tr_transout tr\n" +
                "  WHERE ROWID = ?\n" +
                "    AND tr.clearflag = 1\n" +
                "    AND tr.clearsucessdt IS NOT NULL";
        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                String rowId = rowIds.get(i);
                ps.setString(1, rowId);
            }

            @Override
            public int getBatchSize() {
                return rowIds.size();
            }
        });
    }

    /**
     * 删除tr_transout表数据
     *
     * @param rowId rowId
     * @return 删除数量
     */
    public int deleteTransOut(String rowId) {
        final String sql = "DELETE FROM tr_transout tr WHERE ROWID = ?";
        return jdbcTemplate.update(sql, rowId);
    }


    /**
     * 根据rowId批量删除tr_transout_extend
     *
     * @param rowIds rowId集合
     * @return 批量删除数量
     */
    public int[] batchDeleteTransOutExtend(final List<String> rowIds) {
        final String sql = "DELETE FROM tr_transout_extend e\n" +
                " WHERE e.consumeid = (SELECT tr.consumeid\n" +
                "                        FROM tr_transout tr\n" +
                "                       WHERE tr.clearflag = 1\n" +
                "                         AND tr.clearsucessdt IS NOT NULL\n" +
                "                         AND ROWID = ?)";
        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                String rowId = rowIds.get(i);
                ps.setString(1, rowId);
            }

            @Override
            public int getBatchSize() {
                return rowIds.size();
            }
        });
    }

    /**
     * 删除tr_transout_extend表数据
     *
     * @param rowId rowId
     * @return 删除数量
     */
    public int deleteTransOutExtend(String rowId) {
        final String sql = "DELETE FROM tr_transout_extend e\n" +
                " WHERE e.consumeid =\n" +
                "       (SELECT tr.consumeid FROM tr_transout tr WHERE ROWID = ?)";
        return jdbcTemplate.update(sql, rowId);
    }


    /**
     * 根据rowId批量删除tr_transout_split表数据
     *
     * @param rowIds rowId集合
     * @return 删除数量
     */
    public int[] batchDeleteTransOutSplit(final List<String> rowIds) {
        final String sql = "DELETE FROM tr_transout_split s\n" +
                " WHERE s.consumeid = (SELECT tr.consumeid\n" +
                "                        FROM tr_transout tr\n" +
                "                       WHERE tr.clearflag = 1\n" +
                "                         AND tr.clearsucessdt IS NOT NULL\n" +
                "                         AND ROWID = ?)";
        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                String rowId = rowIds.get(i);
                ps.setString(1, rowId);
            }

            @Override
            public int getBatchSize() {
                return rowIds.size();
            }
        });
    }


    /**
     * 删除tr_transout_split表数据
     *
     * @param rowId rowId
     * @return 删除数量
     */
    public int deleteTransOutSplit(String rowId) {
        final String sql = "DELETE FROM tr_transout_split s\n" +
                " WHERE s.consumeid =\n" +
                "       (SELECT tr.consumeid FROM tr_transout tr WHERE ROWID = ?)";
        return jdbcTemplate.update(sql, rowId);
    }

}
